const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 *  门店采购
 */

// 查询供应商信息
const getStoreSalesPurchaseCustomerInfor = async (ctx, next) => {
    let getBody = ctx.request.body;
    let selectSql = `SELECT KHYH,YHZH,BM,MC,CASE WHEN (ISNULL(DH,'')= '' OR ISNULL(SJ,'')= '') THEN DH+SJ ELSE DH+'/'+SJ END AS DH,DZ,LXR FROM BM_WLDW WHERE DWLX = 1  AND (MC LIKE '%${getBody.GYS}%' OR JP LIKE '%${getBody.GYS}%')`;
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset };
}

// 获取门店要选择的商品信息
const getStoreSalesShopInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let nowTime = baseFn.getNowTimeId().fullDate;
    let agoTime = baseFn.getDayAgo(30);
    let totalSql = ` SELECT COUNT(Y.BM) AS TOTAL FROM BM_YP Y LEFT JOIN (SELECT SUM(KCSL) AS KCSL,YPBM FROM KC_KCDTB WHERE BMBM = '07' AND KCSL>0 GROUP BY YPBM) ZKC ON Y.BM = ZKC.YPBM WHERE (Y.BM LIKE '%${getBody.SPXX}%' OR 
    Y.MC LIKE '%${getBody.SPXX}%' OR Y.JP LIKE '%${getBody.SPXX}%') `;
    let selectSql = `SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Y.BM) AS PX,BL.MC AS LBMC,ZKC.KCSL AS ZBKC,YK.MIN_KC AS AQKC,Y.BM,Y.TM,Y.YXQX,Y.MC,Y.GG,Y.JLDW,Y.JXMC,Y.SCCJ,Y.CDMC,Y.PZWH,Y.MJSL,Y.LSJ,Y.ZDJ,Y.JFGZ,Y.PFJ,
    Y.HYJ,KC.KYSL,KC.KCSL,ZJJ.DJ AS ZJDJ,QTDJ.ZDDJ,ZKC.PJDJ,XL.SL AS XSSL FROM BM_YP Y 
    LEFT JOIN BM_YPLB BL ON Y.LB = BL.BM
    LEFT JOIN (SELECT YPBM,MIN_KC FROM YP_KCYJ WHERE BMBM = '${getBody.BMBM}') YK ON Y.BM = YK.YPBM 
    LEFT JOIN (SELECT SUM(KCSL) AS KCSL,SUM(KYSL) AS KYSL,YPBM  FROM KC_KCDTB WHERE BMBM = '${getBody.BMBM}' AND KCSL>0 GROUP BY YPBM) KC ON Y.BM = KC.YPBM 
    LEFT JOIN (SELECT SUM(KCSL) AS KCSL,YPBM,MIN(CBJJ) AS PJDJ FROM KC_KCDTB WHERE BMBM = '07' AND KCSL>0 GROUP BY YPBM) ZKC ON Y.BM = ZKC.YPBM 
    LEFT JOIN (SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY mx.YPBM ORDER BY zb.RQ DESC) AS num,mx.DJ,mx.YPBM FROM dbo.RKD_ZB AS zb 
    LEFT JOIN dbo.RKD_MX AS mx ON zb.ID=mx.ID WHERE zb.BMBM = '07') t WHERE t.num=1) ZJJ ON Y.BM = ZJJ.YPBM LEFT JOIN (SELECT MIN(DJ) AS ZDDJ,
    YPBM FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID WHERE RZ.BMBM = '07'  GROUP BY YPBM) QTDJ ON Y.BM = QTDJ.YPBM LEFT JOIN (SELECT SUM(SL) AS SL,YPBM FROM XSD_MX SM LEFT JOIN XSD_ZB SZ ON 
    SM.ID = SZ.ID  WHERE SZ.RQ>='${agoTime}' AND SZ.RQ<= '${nowTime}' AND SZ.BMBM = '${getBody.BMBM}' GROUP BY YPBM) XL ON Y.BM = XL.YPBM WHERE (Y.BM LIKE '%${getBody.SPXX}%' OR 
    Y.MC LIKE '%${getBody.SPXX}%' OR Y.JP LIKE '%${getBody.SPXX}%') `;
    if(getBody.BMBM !== '01' && getBody.BMBM !== '07'){
        selectSql += ` AND ZKC.KCSL > 0 `;
        totalSql += ` AND ZKC.KCSL > 0 `;
    }
    if(getBody.searchList.SPXX){
        selectSql += ` AND (Y.BM LIKE '%${getBody.searchList.SPXX}%' OR 
        Y.MC LIKE '%${getBody.searchList.SPXX}%' OR Y.JP LIKE '%${getBody.searchList.SPXX}%') `;
        totalSql += ` AND (Y.BM LIKE '%${getBody.searchList.SPXX}%' OR 
        Y.MC LIKE '%${getBody.searchList.SPXX}%' OR Y.JP LIKE '%${getBody.searchList.SPXX}%') `;
    }
    if(getBody.searchList.SPLB){
        selectSql += ` AND Y.LB = '${getBody.searchList.SPLB}'  `;
        totalSql += ` AND Y.LB = '${getBody.searchList.SPLB}'  `;
    }
    selectSql += `) T1 WHERE T1.PX BETWEEN ${(getBody.page.index - 1) * (getBody.page.size) + 1} AND ${getBody.page.index * (getBody.page.size)}`
    let result = await sql.query(selectSql);
    let totalResult = await sql.query(totalSql);
    for(let item of result.recordset){
        let selectDetailSql = `SELECT KCSL,SCPH,YXQZ FROM KC_KCDTB WHERE BMBM = '07' AND KCSL>0  AND YPBM = '${item.BM}'`;
        let detailResult = await sql.query(selectDetailSql);
        item.detail = detailResult.recordset;
    }
    ctx.body = { result: result.recordset,total:totalResult.recordset[0].TOTAL };
}
// 根据安全库存生成订单
const getSafeOrderShopInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let nowTime = baseFn.getNowTimeId().fullDate;
    let agoTime = baseFn.getDayAgo(30);
    let totalSql = `SELECT COUNT(Y.BM) AS TOTAL FROM BM_YP Y LEFT JOIN (SELECT YPBM,MIN_KC FROM YP_KCYJ WHERE BMBM = '${getBody.BMBM}') YK 
    ON Y.BM = YK.YPBM LEFT JOIN (SELECT SUM(KCSL) AS KCSL,YPBM FROM KC_KCDTB WHERE BMBM = '${getBody.BMBM}' AND KCSL>0 GROUP BY YPBM) KC 
    ON Y.BM = KC.YPBM  WHERE KC.KCSL/YK.MIN_KC < 0.3 AND  KC.KCSL > 0 AND YK.MIN_KC IS NOT NULL`;

    let selectSql = `SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Y.BM) AS PX,(YK.MIN_KC - KC.KCSL) AS DHSL, ZKC.KCSL AS ZBKC,YK.MIN_KC AS AQKC,Y.BM,Y.MC,Y.GG,Y.JLDW,Y.JXMC,Y.SCCJ,Y.CDMC,Y.PZWH,Y.MJSL,Y.LSJ,Y.ZDJ,Y.JFGZ,KC.KCSL,ZJJ.DJ AS ZJDJ,QTDJ.ZDDJ,ZKC.PJDJ,XL.SL AS XSSL,Y.TM,Y.YXQX,Y.PFJ,Y.HYJ,KC.KYSL 
    FROM BM_YP Y LEFT JOIN (SELECT YPBM,MIN_KC FROM YP_KCYJ WHERE BMBM = '${getBody.BMBM}') YK ON Y.BM = YK.YPBM 
    LEFT JOIN (SELECT SUM(KCSL) AS KCSL,YPBM,SUM(KYSL) AS KYSL FROM KC_KCDTB WHERE BMBM = '${getBody.BMBM}' AND KCSL>0 GROUP BY YPBM) KC ON Y.BM = KC.YPBM 
    LEFT JOIN (SELECT SUM(KCSL) AS KCSL,YPBM,MIN(CBJJ) AS PJDJ FROM KC_KCDTB WHERE BMBM = '07' AND KCSL>0 GROUP BY YPBM) ZKC ON Y.BM = ZKC.YPBM 
    LEFT JOIN (SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY mx.YPBM ORDER BY zb.RQ DESC) AS num,mx.DJ,mx.YPBM FROM dbo.RKD_ZB AS zb 
    LEFT JOIN dbo.RKD_MX AS mx ON zb.ID=mx.ID WHERE zb.BMBM = '07') t WHERE t.num=1) ZJJ ON Y.BM = ZJJ.YPBM LEFT JOIN (SELECT MIN(DJ) AS ZDDJ,
    YPBM FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID WHERE RZ.BMBM = '07'  GROUP BY YPBM) QTDJ ON Y.BM = QTDJ.YPBM 
    LEFT JOIN (SELECT SUM(SL) AS SL,YPBM FROM XSD_MX SM LEFT JOIN XSD_ZB SZ ON SM.ID = SZ.ID  WHERE SZ.RQ>='${agoTime}' AND SZ.RQ<= '${nowTime}' AND SZ.BMBM = '${getBody.BMBM}' 
    GROUP BY YPBM) XL ON Y.BM = XL.YPBM WHERE KC.KCSL/YK.MIN_KC < 0.3 AND  KC.KCSL > 0 AND YK.MIN_KC IS NOT NULL) T1 WHERE T1.PX BETWEEN ${(getBody.pageIndex - 1) * (getBody.pageSize) + 1} AND ${getBody.pageIndex * (getBody.pageSize)} `;
    let totalResult = await sql.query(totalSql);

    let result = await sql.query(selectSql);

    ctx.body = { result: result.recordset, total: totalResult.recordset[0].TOTAL };
}

// 根据月销量生成订单
const getSalesOrderShopInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let nowTime = baseFn.getNowTimeId().fullDate;
    let agoTime = baseFn.getDayAgo(30);
    let totalSql = `SELECT COUNT(Y.BM) AS TOTAL FROM BM_YP Y LEFT JOIN (SELECT SUM(SL) AS SL,YPBM FROM XSD_MX SM LEFT JOIN XSD_ZB SZ ON SM.ID = SZ.ID  WHERE SZ.RQ>='${agoTime}' AND SZ.RQ<= '${nowTime}' 
    GROUP BY YPBM) XL ON Y.BM = XL.YPBM LEFT JOIN (SELECT SUM(KCSL) AS KCSL,YPBM FROM KC_KCDTB WHERE BMBM = '${getBody.BMBM}' AND KCSL>0 GROUP BY YPBM) KC 
    ON Y.BM = KC.YPBM  WHERE KC.KCSL/XL.SL < 0.3 AND  KC.KCSL > 0 AND XL.SL IS NOT NULL`;

    let selectSql = `SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Y.BM) AS PX,(XL.SL - KC.KCSL) AS DHSL, ZKC.KCSL AS ZBKC,YK.MIN_KC AS AQKC,Y.BM,Y.MC,Y.GG,Y.JLDW,Y.JXMC,Y.SCCJ,Y.CDMC,Y.PZWH,Y.MJSL,Y.LSJ,Y.ZDJ,Y.JFGZ,KC.KCSL,ZJJ.DJ AS ZJDJ,QTDJ.ZDDJ,ZKC.PJDJ,XL.SL AS XSSL ,Y.TM,Y.YXQX,Y.PFJ,Y.HYJ,KC.KYSL
    FROM BM_YP Y LEFT JOIN (SELECT YPBM,MIN_KC FROM YP_KCYJ WHERE BMBM = '${getBody.BMBM}') YK ON Y.BM = YK.YPBM 
    LEFT JOIN (SELECT SUM(KCSL) AS KCSL,SUM(KYSL) AS KYSL,YPBM FROM KC_KCDTB WHERE BMBM = '${getBody.BMBM}' AND KCSL>0 GROUP BY YPBM) KC ON Y.BM = KC.YPBM
    LEFT JOIN (SELECT SUM(KCSL) AS KCSL,YPBM,MIN(CBJJ) AS PJDJ FROM KC_KCDTB WHERE BMBM = '07' AND KCSL>0 GROUP BY YPBM) ZKC ON Y.BM = ZKC.YPBM  
    LEFT JOIN (SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY mx.YPBM ORDER BY zb.RQ DESC) AS num,mx.DJ,mx.YPBM FROM dbo.RKD_ZB AS zb 
    LEFT JOIN dbo.RKD_MX AS mx ON zb.ID=mx.ID WHERE zb.BMBM = '07') t WHERE t.num=1) ZJJ ON Y.BM = ZJJ.YPBM LEFT JOIN (SELECT MIN(DJ) AS ZDDJ,
    YPBM FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID WHERE RZ.BMBM = '07'  GROUP BY YPBM) QTDJ ON Y.BM = QTDJ.YPBM 
    LEFT JOIN (SELECT SUM(SL) AS SL,YPBM FROM XSD_MX SM LEFT JOIN XSD_ZB SZ ON SM.ID = SZ.ID  WHERE SZ.RQ>='${agoTime}' AND SZ.RQ<= '${nowTime}' AND SZ.BMBM = '${getBody.BMBM}'
    GROUP BY YPBM) XL ON Y.BM = XL.YPBM WHERE KC.KCSL/XL.SL < 0.3 AND  KC.KCSL > 0 AND XL.SL IS NOT NULL) T1 WHERE T1.PX BETWEEN ${(getBody.pageIndex - 1) * (getBody.pageSize) + 1} AND ${getBody.pageIndex * (getBody.pageSize)} `;

    let totalResult = await sql.query(totalSql);

    let result = await sql.query(selectSql);

    ctx.body = { result: result.recordset, total: totalResult.recordset[0].TOTAL };
}



// 获取历史购进的入库单信息
const getStoreSalesPurchaseOldBuyInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let selectSql = `SELECT RZ.RQ,RZ.ID,RZ.DWMC,RM.SL,RM.DJ,RM.JE,DZ.JBRMC AS CGR,SZ.JBRMC AS SHR FROM RKD_MX RM 
    LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN DHD_ZB DZ ON RZ.ID_DHD = DZ.ID LEFT JOIN SHD_ZB SZ ON RZ.ID = SZ.ID WHERE RM.YPBM = '${getBody.YPBM}'`;
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset };
}

// 获取部门信息
const getPartInfo = async (ctx, next) => {
    let result = await sql.query(`SELECT BM,MC,DZ FROM BM_BM `);
    ctx.body = { result: result.recordset };
}

// 获取商品的类别信息
const getAllShopClassify = async (ctx, next) => {
    let result = await sql.query(`SELECT BM,MC FROM BM_YPLB `);
    ctx.body = { result: result.recordset };
}

// 获取当前登录人的部门信息
const getUserPartInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let result = await sql.query(`SELECT BMBM FROM BM_YG WHERE BM = '${getBody.userId}'`);
    ctx.body = { result: result.recordset[0].BMBM };
}



// 根据部门获取采购员的信息
const getStoreSalesStaffInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    console.log(getBody);
    let selectSql = `SELECT BM,MC FROM BM_YG  WHERE BMBM = '${getBody.BMBM}'`;
    if('gw' in getBody){
        selectSql += ` AND GW LIKE '%${getBody.gw}%' `;
    }
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset };
}

// 生成id
const createId = async (userId, tableName) => {
    let lastOrderSql = `SELECT TOP 1 ID FROM ${tableName} WHERE ID LIKE '${baseFn.getNowTimeId().date}${userId}12%' ORDER BY ID DESC`;
    let oldOrder = await sql.query(lastOrderSql);
    let newSerialNum = 1;
    if (oldOrder.recordset.length !== 0) {
        let oldOrderId = oldOrder.recordset[0].ID;
        let oldSerialNum = oldOrderId.slice(oldOrderId.length - 4);
        newSerialNum = Number(oldSerialNum) + 1;
    }
    newSerialNum = newSerialNum.toString();
    newSerialNum = '0000'.slice(0, 4 - newSerialNum.length) + newSerialNum;
    return baseFn.getNowTimeId().date + userId + '12' + newSerialNum;
}

//保存门店采购的信息
const keepStoreSalesPurchaseInfo = async (ctx, next) => {
    try {
        let getBody = ctx.request.body;
        let shopList = getBody.shopList;
        let searchList = getBody.searchList;
        let customer = searchList.customer;
        // 生成订货单id
        let newId = await createId(searchList.userId, 'DHD_ZB');

        /**
         *    写入订货单明细表
         */

        let insertDhd_MxSql = `INSERT INTO DHD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,YXQX,JLDW,MJSL,SL,JS,SS,YJ,YJ_HS,DJ,DJ_HS,
        JE,HJ,KCSL,YDSL,WDSL,KYSL,PFJ,LSJ,HYJ,CB)`;
        let zje = 0, zss = 0, zjs = 0, zsl = 0, zhj = 0, zcb = 0, zml = 0;

        shopList.forEach(async (item, ind) => {

            let dj = Number(item.DJ ? item.DJ : 0);
            let sl = Number(item.DHSL ? item.DHSL : 0);

            let mjsl = Number(item.MJSL ? item.MJSL : 0);
            let js = mjsl === 0 ? 0 : Math.floor(DHSL / mjsl);
            let ss = sl - (mjsl * js);
            let je = (dj * sl).toFixed(2);

            zsl += sl;
            zje += Number(je);
            zss += Number(ss);
            zjs += Number(js);

            let xhSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);

            // 写入订货单明细
            insertDhd_MxSql += `SELECT '${newId}',${baseFn.getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.BM}','${item.TM}','${item.MC}','${item.GG}'
            ,'${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}',${item.YXQX},'${item.JLDW}',${item.MJSL},${item.DHSL},${js},${ss},
            ${item.DJ},${item.DJ},${item.DJ},${item.DJ},${je},${je},${item.KCSL},${item.DHSL},0,${item.KYSL},${item.PFJ},${item.LSJ},${item.HYJ},'A' `;

            if (ind !== (shopList.length - 1)) {
                insertDhd_MxSql += ` UNION `;
            }

        });

        // 通过结算方式编码获取结算方式信息
        let jsfsResult = await sql.query(`SELECT KM1BM,KMBM,KMQC FROM BM_JSFS WHERE BM = '${searchList.JSFS}'`);
        let jsfsInfo = jsfsResult.recordset[0];

        /**
        *   插入订货单结算表
        */
        let insertDhd_JsSql = `INSERT INTO DHD_JS(ID,XH,BM,MC, KM1BM,KMBM,KMQC,JSJE) VALUES ('${newId}',1,'${searchList.JSFS}','${searchList.JSFSMC}',
        '${jsfsInfo.KM1BM}','${jsfsInfo.KMBM}','${jsfsInfo.KMQC}',${zje})`;

        /**
        *   插入订货单总表
        */

        let insertDhd_ZbSql = `INSERT INTO DHD_ZB(ID,RQ,DWBM,DWMC,BMBM,BMMC,JBRBM,JBRMC,JSFSBM,JSFSMC,DHDD,JHRQ,LXR,LXDH,HTH,ZDR,SHR,ZSL,
            ZJS,ZSS,ZJE,ZSE,ZHJ,YJS,EJS,WJS,BZ,SH) VALUES ('${newId}','${baseFn.getNowTimeId().fullDate}','${customer.BM}',
            '${customer.MC}','${searchList.BMBM}','${searchList.BMMC}','${searchList.CGY}','${searchList.CGYMC}','${searchList.JSFS}',
            '${searchList.JSFSMC}','${customer.DZ}','${searchList.DHRQ}','${searchList.LXR}','${searchList.LXDH}','${searchList.HTH}',
            '${searchList.ZDR}','${searchList.ZDR}',${zsl},${zjs},${zss},${zje},0,${zje},${zje},0,${zje},'${searchList.BZ}','F')`;


        // 几个表同时插入，产生错误就回滚
        let lastSql = `SET XACT_ABORT ON Begin Tran ${insertDhd_ZbSql} ${insertDhd_MxSql} ${insertDhd_JsSql} Commit Tran`;
        let lastResult = await sql.query(lastSql);
        if (lastResult.rowsAffected[0] > 0) {
            ctx.body = { success: true }
        } else {
            ctx.body = { success: false }
        }
    } catch (err) {
        ctx.body = { success: false, err }
    }


}





module.exports = {
    'POST /getStoreSalesPurchaseCustomerInfor': getStoreSalesPurchaseCustomerInfor,
    'POST /getStoreSalesShopInfo': getStoreSalesShopInfo,
    'POST /getStoreSalesPurchaseOldBuyInfo': getStoreSalesPurchaseOldBuyInfo,
    'POST /getSafeOrderShopInfo': getSafeOrderShopInfo,
    'POST /getSalesOrderShopInfo': getSalesOrderShopInfo,
    'GET /getPartInfo': getPartInfo,
    'GET /getAllShopClassify': getAllShopClassify,
    'POST /getStoreSalesStaffInfo': getStoreSalesStaffInfo,
    'POST /keepStoreSalesPurchaseInfo': keepStoreSalesPurchaseInfo,
    'POST /getUserPartInfo': getUserPartInfo,
    // 'POST /getContainHempLimitcount': getContainHempLimitcount


}